USE ASLB 
GO
IF (OBJECTPROPERTY ( OBJECT_ID('GetSession'), 'IsProcedure' ) IS NOT NULL)
BEGIN
   DROP PROC GetSession
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE PROC [dbo].[GetSession] 
       @DatabaseName Varchar(50)
       ,@UserName varchar(100)
       ,@IPAddress varchar(100)
       ,@Params varchar(100)
       ,@URL varchar(200) output
       ,@UseTCPIP BIT = 0
       
AS
/***********************************************************************************
** Name      : GetSession
**
** File Name : GetSession.sql
**
** Desc      : 
**
** Called By : 
**
** Calls     : 
**
** Returns   :
** Author    : jocaplan
**
** Date      : 03/30/2009
**
** Parameters: 
                

** Usage     :
               

************************************************************************************
** Change History
************************************************************************************
** Date        Author     Description
** ----------  ---------  ----------------------------------------------------------
**********************************************************************************/

SET NOCOUNT ON

DECLARE
   @SessionID int
   ,@DatabaseID int
   ,@ServerID int
   


-- Check to see if there is an existing session
SELECT
   @SessionID = S.SessionID
   ,@ServerID = SE.ServerID
   ,@URL = CASE WHEN @UseTCPIP = 0 THEN SE.URL
         ELSE SE.Name
         END
FROM
   [Session] S
      JOIN [Database] D
         ON S.DatabaseID = D.DatabaseID
      JOIN [Server] SE
         ON S.ServerID = SE.ServerID
WHERE
   D.Name = @DatabaseName
   and S.UserName = @UserName
   and S.IPAddress = @IPAddress
   and ISNULL(S.Params,'') = ISNULL(@Params,'')
   
-- If there is an existing session update the LastRequestDate
IF @SessionID is not null
BEGIN
   UPDATE [Session]
   SET LastRequestDate = GETDATE()
   WHERE SessionID = @SessionID
   
   UPDATE [Server]
   SET LastRequestDate = GETDATE()
   WHERE ServerID = @ServerID
END


-- If no Session exists, create a new one
IF @SessionID is NULL
BEGIN

   SELECT top 1
      @ServerID = SE.ServerID
      ,@DatabaseID = D.DatabaseID
      ,@URL = CASE WHEN @UseTCPIP = 0 THEN SE.URL
         ELSE SE.Name
         END
   FROM
      [Server] SE
         JOIN ServerDatabase SD
            ON SE.ServerID = SD.ServerID
         JOIN [Database] D
            ON SD.DatabaseID = D.DatabaseID
   WHERE
      D.Name = @DatabaseName
      AND SE.Disabled = 0
      AND SE.Offline = 0
      AND SD.Disabled = 0
      AND D.Disabled = 0
      
   ORDER BY
      SE.[Load]
      ,SE.LastRequestDate
      
   IF @ServerID IS NOT NULL
   BEGIN
   
      INSERT INTO [Session]
      SELECT
         ISNULL(MAX(SessionID),0) + 1
         ,@ServerID
         ,@DatabaseID
         ,@UserName
         ,@IPAddress
         ,@Params
         ,GETDATE()
         ,GETDATE()
      FROM [Session]

      UPDATE [Server]
      SET LastRequestDate = GETDATE()
      WHERE ServerID = @ServerID
      
   END
   ELSE
      SELECT @URL = NULL
END

IF @UseTCPIP = 1
BEGIN
   SELECT @URL as ServerName
END